package com.atsmart.app.sql;

import java.util.ArrayList;
import java.util.List;

import com.atsmart.app.model.TemplateDetailInfo;
import com.atsmart.app.model.TemplateItemInfo;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
/**
 * TODO SQL模板的详细信息
 */
public class TemplateDetailSqlHelper {

	private SQLiteDatabase db = null;

	/**
	 * 打开数据库
	 */
	public void open(SqlHelper dh) {
		// dh = new SqlHelper(null, "atsmart", null,SqlHelper.getDBVersion());
		db = dh.getWritableDatabase();
	}

	public synchronized void addTemplateDetailInfo(TemplateItemInfo at,int tem_id,int type) {
		if (at == null) {
			return;
		}
		String strSql = "insert or replace into templateDetail_info(item_id,name,position,type,tem_id,required_flag,type_flag) values(?,?,?,?,?,?,?);";
		try {
			SQLiteStatement stm = db.compileStatement(strSql);
			stm.bindLong(1, at.getId());
			stm.bindString(2, at.getName());
			stm.bindLong(3, at.getPosition());
			stm.bindLong(4, at.getType());
			stm.bindLong(5, tem_id);
			stm.bindLong(6, at.getRequired_flag());
			stm.bindLong(7, type);
			stm.execute();
			stm.clearBindings();
			stm.close();
		} catch (Exception e) {
			// will not happen
		}
	}
	
	public synchronized void addTemplateDetailInfo(TemplateItemInfo at,int tem_id) {
		if (at == null) {
			return;
		}
		String strSql = "insert or replace into templateDetail_info(item_id,name,position,type,tem_id,required_flag,type_flag) values(?,?,?,?,?,?,?);";
		try {
			SQLiteStatement stm = db.compileStatement(strSql);
			stm.bindLong(1, at.getId());
			stm.bindString(2, at.getName());
			stm.bindLong(3, at.getPosition());
			stm.bindLong(4, at.getType());
			stm.bindLong(5, tem_id);
			stm.bindLong(6, at.getRequired_flag());
			stm.bindLong(7, at.getType());
			stm.execute();
			stm.clearBindings();
			stm.close();
		} catch (Exception e) {
			// will not happen
		}
	}

	public TemplateDetailInfo getTemplateDetailInfo(int tem_id,int type) {
		String sql = " select _id,item_id,name,position,type,tem_id,required_flag,type_flag from templateDetail_info where tem_id= "
				+ tem_id  +" and type_flag ="+type;
		Cursor cursor = db.rawQuery(sql, null);
		TemplateItemInfo historie = null;
		List<TemplateItemInfo> nums = null;
		TemplateDetailInfo t = null;
		try {
			if (cursor != null && cursor.moveToFirst()) {
				nums = new ArrayList<TemplateItemInfo>();
				t = new TemplateDetailInfo();
				for (int i = 0; i < cursor.getCount(); i++) {
					historie = new TemplateItemInfo();
					historie.setId(cursor.getInt(1));
					historie.setName(cursor.getString(2));
					historie.setPosition(cursor.getInt(3));
					historie.setType(cursor.getInt(4));
					historie.setRequired_flag(cursor.getInt(5));
					nums.add(historie);
					cursor.moveToNext();
				}
				t.setItems(nums);
			}
		} catch (Exception e) {
			return null;
		} finally {
			cursor.close();
		}
		return t;
	}
	public TemplateDetailInfo getTemplateDetailInfo(int tem_id) {
		String sql = "select _id,item_id,name,position,type,tem_id,required_flag,type_flag from templateDetail_info where tem_id=" + tem_id;
		Cursor cursor = db.rawQuery(sql, null);
		TemplateItemInfo historie = null;
		List<TemplateItemInfo> nums = null;
		TemplateDetailInfo t = null;
		try {
			if (cursor != null && cursor.moveToFirst()) {
				nums = new ArrayList<TemplateItemInfo>();
				t = new TemplateDetailInfo();
				for (int i = 0; i < cursor.getCount(); i++) {
					historie = new TemplateItemInfo();
					historie.setId(cursor.getInt(1));
					historie.setName(cursor.getString(2));
					historie.setPosition(cursor.getInt(3));
					historie.setType(cursor.getInt(4));
					
					historie.setRequired_flag(cursor.getInt(6));
					nums.add(historie);
					cursor.moveToNext();
				}
				t.setItems(nums);
			}
		} catch (Exception e) {
			return null;
		} finally {
			cursor.close();
		}
		return t;
	}
	
	public int getCount() {
		String sql = " select _id,item_id,name,position,type from templateDetail_info ";
		Cursor cursor = db.rawQuery(sql, null);
		try {
			if (cursor != null && cursor.moveToFirst()) {
				return cursor.getCount();
			}
		} catch (Exception e) {
			return 0;
		} finally {
			cursor.close();
		}
		return 0;
	}

	public int getCount(int item_id) {
		String sql = "select _id,item_id,name,position,type,tem_id,required_flag,type_flag from templateDetail_info where tem_id= "
				+ item_id;
		Cursor cursor = db.rawQuery(sql, null);
		try {
			if (cursor != null && cursor.moveToFirst()) {
				 return cursor.getCount();
			}
		} catch (Exception e) {
			return 0;
		} finally {
			cursor.close();
		}
		return 0;
	}
	
	public void deleteAttribute(int item_id,int type_flag) {
		// db.delete("position_attribute", null, null);
		db.execSQL("delete from templateDetail_info where item_id="+item_id+" and type_flag="+type_flag);
	}

	public void delete(int templateID) {
		db.execSQL("delete from templateDetail_info where tem_id="+templateID);
		
	}
}
